<?php
require_once("include/database.php");
$db = db_connect();

function credentials_valid($username, $password) {
  global $db;
  $query = $db->prepare("SELECT `Username`, `Password` 
            FROM `regular_user` 
            WHERE `Username` = :user");
  $query->execute(array("user" => $username));

  if ($user = $query->fetch()) {
    // array of the user and their information. 
    $password_requested = $password;
    if($password_requested === $user['Password']){
      return $user;
    }
  }
  return false;
}

// Logs into the user $user
function log_in($username){
  $_SESSION['username'] = $username;
}

// Returns the currently logged in user (if any)
function current_user(){
    session_start();
    global $db;
    static $current_user;

    if(!$current_user){
        if($_SESSION['username']){
            $username = $_SESSION['username'];
            $query = $db->prepare("SELECT *
                FROM `regular_user` 
                WHERE `Username` = :user");
            $query->execute(array("user" => $username));

            if($result = $query->fetch()){
                $current_user = $result;

                // Check if you are administrator.
                $adminquery = $db->prepare("SELECT * FROM administrators WHERE Username = :user");
                $adminquery->execute(array("user" => $username));
                if ($adminresult = $adminquery->fetch()) {
                    $current_user["type"] = 2;
                    return $current_user;
                }
                $profquery = $db->prepare("SELECT fac.*,dep.Dept_Name AS Department FROM faculty as fac LEFT JOIN department as dep ON fac.Dept_ID = dep.Dept_ID WHERE Username = :user");
                $profquery->execute(array("user" => $username));
                if ($profresult = $profquery->fetch()) {
                    // Obtain research interests.
                    $riquery = $db->prepare("SELECT GROUP_CONCAT(DISTINCT Research_Interest SEPARATOR ', ') AS Research_Interests FROM faculty_research_interests WHERE Instructor_ID = :id");
                    $riquery->execute(array("id" => $profresult['Instructor_ID']));
                    $res = $riquery->fetch();
                    $current_user['Research_Interests'] = $res['Research_Interests'];
                    $current_user["type"] = 1;
                    $current_user["facultyid"] = $profresult['Instructor_ID'];
                    $current_user["Department"] = $profresult['Department'];
                    $current_user["Dept_ID"] = $profresult['Dept_ID'];
                    $current_user["Position"] = $profresult['Position'];
                    return $current_user;
                }
                $studentquery = $db->prepare("SELECT * FROM student WHERE Username = :user");
                $studentquery->execute(array("user" => $username));
                if ($studentresult = $studentquery->fetch()) {
                    $current_user["type"] = 0;
                    $current_user['studentid'] = $studentresult["Student_ID"];
                    $current_user['Tutor'] = $studentresult['Tutor'];
                    return $current_user;
                }
            }
        }
    }
    return $current_user;
}
// Requires a current user
function require_login(){
  if(!current_user()){
    $_SESSION['redirect_to'] = $_SERVER["REQUEST_URI"];
    header("Location: signin.php?login_required=1");
    exit("You must log in.");
  }
}

function require_user($num) {
  global $db;
  $current_user = current_user();

  if ($current_user['type'] != $num) {
      header("Location: index.php?wrong_authentication=1");
      exit("Wrong authentication permissions.");
  }
}

//creates a new user account
function create_account( $userName, $password, $userType )
{
    global $db;

    $resultquery = $db->prepare("select * from regular_user where Username = :user");
    $resultquery->execute(array("user" => $userName));
    $result = $resultquery->fetch();

    if ($result) {
        // the account already exists, let user know
        return null;  // the account already exists and you can't create it.

    } else {
        //insert into regular user first
        $insert_query = $db->prepare( "insert into regular_user (Username, Password)
                                values ( :username, :password )");
        $insert_query->execute(array("username" => $userName,"password" => $password ));
        $result = $insert_query->fetchAll(PDO::FETCH_CLASS,"stdClass");

        //insert into student table if student
        if($userType == 1){

            $insert_student_query = $db->prepare( "insert into student (Username)
                                values ( :username )");
            $insert_student_query->execute(array("username" => $userName ));
            $insert_student_query->fetchAll(PDO::FETCH_CLASS,"stdClass");

        }
        //faculty member
        else{
            $insert_faculty_query = $db->prepare( "insert into faculty (Username)
                                values ( :username )");
            $insert_faculty_query->execute(array("username" => $userName ));
            $insert_faculty_query->fetchAll(PDO::FETCH_CLASS,"stdClass");
        }

    }

    return credentials_valid( $userName, $password);
}

//$paramArray can be null or an associative array with keys and values
 function redirect($phpFile, $paramArray=null){
    $params = array();
    if ($paramArray == null){

        header("Location: ".$phpFile);
    }
    else{
        foreach($paramArray as $key => $value) {
            $params[] = $key."=".urlencode($value);
        }
        $msg = join('&', $params);
        header("Location: ".$phpFile."?".$msg);

    }
}
//get all the departments
function getDepartments(){
    global $db;
    // Get all the departments
    $dept_query = $db->prepare("SELECT * FROM department");
    $dept_query->execute();
    $departments = $dept_query->fetchAll(PDO::FETCH_CLASS,"stdClass");
    return $departments;


}
function getCourse($dept_id){
    global $db;
    $course_query = $db->prepare("SELECT * FROM offers WHERE Dept_ID=:dept_id");
    $course_query->execute(array("dept_id" => $dept_id));
    $courses = $course_query->fetchAll(PDO::FETCH_CLASS,"stdClass");
    return $courses;

}

function getSection($course_title, $course_term){
    global $db;

    $section_query = $db->prepare("SELECT * FROM section WHERE Course_Title=:course_title AND Term=:course_term");
    $section_query->execute(array("course_title" => $course_title, "course_term" => $course_term));
    $sections = $section_query->fetchAll(PDO::FETCH_CLASS,"stdClass");
    return $sections;

}

function getTerms($course_title){
    global $db;

    $term_query = $db->prepare("SELECT DISTINCT Term FROM section WHERE Course_Title=:course_title");
    $term_query->execute(array("course_title" => $course_title));
    $terms = $term_query->fetchAll(PDO::FETCH_CLASS,"stdClass");
    return $terms;

}
//get classes with a B or better
function getClassesForTutoring($userName){
    global $db;

    $classes_query = $db->prepare("SELECT * FROM student as s,
    registers as r, section as t WHERE s.Student_ID = r.Student_ID
    and s.Username=:userName and (r.Grade_Received = 'A' or r.Grade_Received = 'B')and (t.CRN = r.CRN)");
    $classes_query->execute(array("userName" => $userName));
    $classes_to_tutor = $classes_query->fetchAll(PDO::FETCH_CLASS,"stdClass");
    return $classes_to_tutor;

}

function getStudent($userName){
    global $db;

    $student_query = $db->prepare("SELECT * FROM student WHERE Username=:userName");
    $student_query->execute(array("userName" => $userName));
    $studentInfo = $student_query->fetchAll(PDO::FETCH_CLASS,"stdClass");

    return $studentInfo[0];

}

function updateUser ($userName,$name,$dob, $gender, $address, $perm_address, $contact_no, $email ){
    global $db;
    $user_query = $db->prepare("UPDATE regular_user SET Name=:name, Date_Of_Birth=:dob,
    Address=:address, Permanent_Address=:perm_address, Contact_No=:contact_no,
    Email_ID=:email, Gender=:gender WHERE Username=:username");

    $user_query->execute(array("username" => $userName, "name"=>$name, "dob" => $dob, "address" => $address,
    "perm_address" => $perm_address, "contact_no" => $contact_no, "email" => $email, "gender" => $gender));

    return $user_query;

}
//updates the student
function updateStudent ($userName,$major, $degree ){
    global $db;
    $student_query = $db->prepare("UPDATE student SET Major=:major, Degree=:degree WHERE Username=:username");

    $student_query->execute(array("username" => $userName, "major"=>$major, "degree" => $degree));

    return $student_query;

}

function deleteEduHistory($userName){
    global $db;
    $student = getStudent($userName);
    $student_id = $student->Student_ID;

    $education_query = $db->prepare("DELETE FROM education_history WHERE Student_ID=:student_id");

    $education_query->execute(array("student_id" => $student_id));


}
 //sets a new education history;
function setEduHistory($userName, $name_inst, $major, $degree, $grad, $gpa){
    global $db;
    $student = getStudent($userName);
    $student_id = $student->Student_ID;

    $insert_query = $db->prepare( "insert into education_history (Student_ID, Name_Of_School,
      Year_Graduation, Degree, Major, GPA)
                                values ( :student_id, :name_inst, :yr_grad, :degree, :major,:gpa )");
    $insert_query->execute(array("student_id" => $student_id,"name_inst" => $name_inst, "yr_grad" => $grad,
                            "degree" => $degree, "major" =>$major, "gpa" => $gpa));
    $result = $insert_query->fetchAll(PDO::FETCH_CLASS,"stdClass");
    return $result;



}
function getEduHistory($userName){
    global $db;
    $student = getStudent($userName);
    $student_id = $student->Student_ID;

    $ed_history_query = $db->prepare( "select * from education_history where Student_ID=:student_id");
    $ed_history_query->execute(array("student_id" => $student_id));
    $result = $ed_history_query->fetchAll(PDO::FETCH_CLASS,"stdClass");
    return $result;


}

function updateTutorInfo($userName, $isTutor, $classes){

    global $db;
    $student_query = $db->prepare("UPDATE student SET Tutor=:tutor WHERE Username=:username");
    $student_query->execute(array("username" => $userName, "tutor"=>$isTutor? 1:0));

    //delete classes
    $student = getStudent($userName);
    $student_id = $student->Student_ID;

    $delete_classes = $db->prepare("DELETE FROM tutors_for WHERE Tutor_Student_ID=:student_id");
    $delete_classes->execute(array("student_id" => $student_id));

    //if is a tutor get the classes and add
    if ( ($isTutor) && ($classes!="") ){
        foreach($classes as $className){
            $insert_query = $db->prepare( "insert into tutors_for (Tutor_Student_ID, Course_Title)
                                values ( :student_id, :course_title)");
            $insert_query->execute(array("student_id" => $student_id,"course_title" => $className));
            $result = $insert_query->fetchAll(PDO::FETCH_CLASS,"stdClass");

        }
    }

}




?>